<?php

if (!defined('BASEPATH'))
    exit('No direct script access allowed');

class Monitor_mod extends CI_Model {

    private $_dbTableMessages = 'messages';
    private $_dbTableUsers = 'users';
    private $_dbTableImages = 'images';
    function __construct() {
        // Call the Model constructor
        parent::__construct();
    }

    public function getMessages() {
        $query = $this->db->select
                        ("from as id_from, to as id_to, time, content,"
                        . " (SELECT username FROM {$this->_dbTableUsers} WHERE {$this->_dbTableUsers}.`id` = {$this->_dbTableMessages}.`from`) as username_from, "
                        . " (SELECT username FROM {$this->_dbTableUsers} WHERE {$this->_dbTableUsers}.`id` = {$this->_dbTableMessages}.`to` ) as username_to, "
                        . "( SELECT name  FROM {$this->_dbTableImages} WHERE `private`=0 and {$this->_dbTableImages}.`user_id`= {$this->_dbTableMessages}.`from` ORDER BY main desc LIMIT 1 ) as image_name_from, "
                       . "( SELECT name  FROM {$this->_dbTableImages} WHERE `private`=0 and {$this->_dbTableImages}.`user_id`= {$this->_dbTableMessages}.`to` ORDER BY main desc LIMIT 1 ) as image_name_to "
                )
                ->from($this->_dbTableMessages)
                ->from($this->_dbTableUsers)
                ->order_by( $this->_dbTableMessages.'.id', 'desc')
                ->distinct()
                ->get();
        return $query->result();
    }

    public function getFilteredMessages($filter = NULL) {
        $where = "";

        if (isset($filter['interval']) && ($filter['interval'] != 0)) {
           
            $where .= "( ";
            $where .= $this->_dbTableMessages . ".time >= DATE_SUB( NOW( ) , INTERVAL " . $filter['interval'] . " HOUR )";
            $where .= " )";
        } else if (isset($filter['interval']) && ($filter['interval'] == 0)) {
            
            if (isset($filter['date_from']) && $filter['date_from'] != "") {

                $where .= "( ";
                $where .= $this->_dbTableMessages . ".time >= '" . $filter['date_from'] . " 00:00";
                $where .= "' )";
            }
            if (isset($filter['date_to']) && $filter['date_to'] != "") {
                if ($where != "") {
                $where .= " AND ";
                }
                $where .= "( " . $this->_dbTableMessages . ".time <='" . $filter['date_to']. " 23:59";
                $where .= "' ) ";
            }
            if (isset($filter['time_from']) && ($filter['time_from'] != "")) {
                if ($where != "") {
                $where .= " AND ";
                }
                $where .= "( ";
                $where .= "HOUR(" . $this->_dbTableMessages . ".time) >= '" . $filter['time_from'];
                $where .= "' )";
            }
            if (isset($filter['time_to']) && $filter['time_to'] != "") {
                if ($where != "") {
                $where .= " AND ";
                }
                $where .= "( HOUR(" . $this->_dbTableMessages . ".time) <='" . $filter['time_to'];
                $where .= "' ) ";
            }
        }
        $this->db->select
                ("from as id_from, to as id_to, time, content,"
                . " (SELECT username FROM {$this->_dbTableUsers} WHERE {$this->_dbTableUsers}.`id` = {$this->_dbTableMessages}.`from`) as username_from, "
                . " (SELECT username FROM {$this->_dbTableUsers} WHERE {$this->_dbTableUsers}.`id` = {$this->_dbTableMessages}.`to` ) as username_to, "
                . "( SELECT name  FROM {$this->_dbTableImages} WHERE `private`=0 and {$this->_dbTableImages}.`user_id`= {$this->_dbTableMessages}.`from` ORDER BY main desc  LIMIT 1) as image_name_from, "
                . "( SELECT name  FROM {$this->_dbTableImages} WHERE `private`=0 and {$this->_dbTableImages}.`user_id`= {$this->_dbTableMessages}.`to` ORDER BY main desc  LIMIT 1) as image_name_to "
        );
        $this->db->from($this->_dbTableMessages);
        $this->db->from($this->_dbTableUsers);
        if ($where != "")
            $this->db->where($where);
        $this->db->order_by( $this->_dbTableMessages.'.id', 'desc');
        $this->db->distinct();
        $query = $this->db->get();
        return $query->result();
    }

}

?>
